Database reference guide

HOME

Expressions limits and constraints

This section looks at limits of NSQL expressions.

Expression Length

The maximum length of an expression is 64K.

Switch Statement

The maximum number of case statements that can be included in a switch statement is 100.

There is no limit on the maximum length of each case label. However, the maximum allowed length of the expression must not be exceeded by any Switch statement.

There is no limit on the overall length of a switch statement.

There is no limit on the maximum width of Switch Evaluation clause.

Time to calculate

The slowest part of an expression calculation is the time taken to read data to and from disk. The logical operators are also slower to execute than the intrinsic functions, for example IF…THEN…ELSE, SWITCH…

Memory Per Expression

Whilst there is a 64k limit on the length of an expression, in reality, the maximum length is more likely to be determined by the memory that is required to execute the expression.

When estimating the memory that is required to calculate an expression, the following can be used:

For each intermediate result used in the expression, the memory required is:

16000(Size of Input Buffer + Size of output Buffer)

So the following expression would require 0.12 mb of memory to calculate:

Select * from TABLE where INCOME> OUTGOINGS/2;

If INCOME and OUTGOINGS are both integer fields requiring 4 bytes per record, Input + Output = 8, therefore the memory requirement is 0.12Mb which is minimal.

The more nested an expression, the more memory that is required. Also, expressions that manipulate long text strings use a lot more memory.

Storage Requirements of Expressions

The amount of memory required to store an expression depends on the data type of the resulting expression. For details of this, see section on Data Types.

Time to Create Expression

The time taken to create and index an expression depends on the memory that is available to the Engine process. In Engine 3.1 and later, the free memory available to Engine can obtained via use of the ND_GDI_AVAILABLEPHYSICAL dispatch call.

Column Widths

When creating derived data by concatenating existing columns, the resulting width will be the maximum width of the new column, rather than the sum of the 2 source column widths.

Consider the following expression:

[Demo].[Cust].[Surname] + "_" + [Demo].[Forename]

If column [SURNAME] has a defined with of 20, but the maximum actual width is 18, and if column [FORENAME] has a defined width of 20, but again the maximum width is 18, the width of the new column will be 36, as opposed to 40.

  Online & Instructor-Led Courses | Training Videos | Webinar Recordings
© Alterian. All Rights Reserved. | Privacy Policy | Legal Notice